
if exists (select 1 from sysobjects where name = 'vw_schemadata' and type = 'V')
begin
   drop view vw_schemadata
   print 'View: vw_schemadata deleted ...'
end
go
create view vw_schemadata
as

   select schemaid = ps.SchemaID, 
          schemaname = s.Name, 
          prsschemaid = ps.PrsSchemaID,  
          prsid = ps.PrsID,
          nachname = isnull(p.Nachname,''),
          vorname = isnull(p.Vorname,''),
          abteilungid = ps.AbteilungID,
          abteilungname = ab.Name,
          rgb = ps.RGB,
          color = ps.ColorName
     from PlanSchema ps 
    inner join Schemas s on ps.SchemaID = s.SchemaID
     left outer join Person p
       on p.PrsId = ps.PrsID
    inner join Abteilung ab
       on ab.AbteilungID = ps.AbteilungID

go
print 'View: vw_schemadata done ...'
go

grant select on vw_schemadata to prsadmins with grant option
go
grant select on vw_schemadata to prsusers
go

